﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace DuJianliang.ApplicationPlatform.UserPermission
{
    public class UserPermissionDal
    {
        public DeptOB GetDeptByUserID(string userID)
        {
            string sql = @"SELECT * FROM [dbo].[t5715_Department] WHERE DeptID IN (SELECT DeptID FROM [dbo].[t5715_User] WHERE UserID = @UserID)";
            
            try
            {
                DeptOB deptOB = new DeptOB();
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@UserID", DbType.String, userID);
                
                using (IDataReader dataReader = database.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        deptOB.DeptID = dataReader["DeptID"] == null ? string.Empty : dataReader["DeptID"].ToString();
                        deptOB.DeptName = dataReader["DeptName"] == null ? string.Empty : dataReader["DeptName"].ToString();
                        deptOB.ParentDeptID = dataReader["ParentDeptID"] == null ? string.Empty : dataReader["ParentDeptID"].ToString();
                        deptOB.DeptLevel = dataReader["DeptLevel"] == null ? string.Empty : dataReader["DeptLevel"].ToString();
                    }
                }
                return deptOB;
            }
            catch (Exception ex)
            {
                throw ex;
            }            
        }

        public DeptOB GetDeptByID(string deptID)
        {
            string sql = @"SELECT * FROM [dbo].[t5715_Department] WHERE DeptID = @DeptID";

            try
            {
                DeptOB deptOB = new DeptOB();
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@DeptID", DbType.String, deptID);

                using (IDataReader dataReader = database.ExecuteReader(dbCommand))
                {
                    if (dataReader.Read())
                    {
                        deptOB.DeptID = dataReader["DeptID"] == null ? string.Empty : dataReader["DeptID"].ToString();
                        deptOB.DeptName = dataReader["DeptName"] == null ? string.Empty : dataReader["DeptName"].ToString();
                        deptOB.ParentDeptID = dataReader["ParentDeptID"] == null ? string.Empty : dataReader["ParentDeptID"].ToString();
                        deptOB.DeptLevel = dataReader["DeptLevel"] == null ? string.Empty : dataReader["DeptLevel"].ToString();
                    }
                }
                return deptOB;
            }
            catch (Exception ex)
            {
                throw ex;
            }      
        }

        public DataTable GetDeptDataTable()
        {
            string sql = @"SELECT * FROM [dbo].[t5715_Department]";

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                return database.ExecuteDataSet(dbCommand).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool HasPermission(string userID, int permissionID)
        {
            string sql = @"SELECT COUNT(ID) FROM dbo.t5715_UserPermission WHERE UserID = @UserID AND PermissionID = @PermissionID";

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@UserID", DbType.String, userID);
                database.AddInParameter(dbCommand, "@PermissionID", DbType.Int16, permissionID);
                string count = database.ExecuteScalar(dbCommand).ToString();
                return count.Equals("0") ? false : true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool HasSameUsername(string username)
        {
            string sql = @"SELECT COUNT(UserID) FROM dbo.t5715_User WHERE UserName = @UserName";

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@UserName", DbType.String, username);
                string count = database.ExecuteScalar(dbCommand).ToString();
                return count.Equals("0") ? false : true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool IsAdmin(string userID)
        {
            string sql_select = @"SELECT COUNT(UserID) FROM dbo.t5715_User WHERE IsAdmin = 1 AND UserID = @ID";

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql_select);
                database.AddInParameter(dbCommand, "@ID", DbType.String, userID);
                string count = database.ExecuteScalar(dbCommand).ToString();
                return !count.Equals("0");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool hasOtherAdmin(string id)
        {
            string sql_select = @"SELECT COUNT(UserID) FROM dbo.t5715_User WHERE IsAdmin = 1 AND UserID <> @ID";

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql_select);
                database.AddInParameter(dbCommand, "@ID", DbType.String, id);
                string count = database.ExecuteScalar(dbCommand).ToString();
                return !count.Equals("0");
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetPermissionDataTable()
        {
            string sql = @"SELECT * FROM [dbo].[t5715_Permission] ORDER BY OrderNumber";

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                return database.ExecuteDataSet(dbCommand).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GetUserPermissionDataTable(string userID)
        {
            string sql = @"SELECT * FROM dbo.t5715_UserPermission WHERE UserID  = @UserID";

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@UserID", DbType.String, userID);
                return database.ExecuteDataSet(dbCommand).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}